package com.example.yingjiguanli_demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.yingjiguanli_demo.pojo.Rankings;
import com.example.yingjiguanli_demo.pojo.UserRankingDTO;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author scj
 * @since 2024-11-04
 */
@Mapper
public interface RankingsMapper extends BaseMapper<Rankings> {
    /**
     * 更新用户成绩
     *
     * @param userId     用户id
     * @param subjectId  科目id
     * @param scoreToAdd 成绩
     * @return int
     */
    @Update("UPDATE rankings SET score = score + #{scoreToAdd} WHERE user_id = #{userId} AND subject_id = #{subjectId}")
    int updateScore(@Param("userId") int userId, @Param("subjectId") int subjectId, @Param("scoreToAdd") int scoreToAdd);

    /**
     * 插入成绩
     *
     * @param userId    用户id
     * @param subjectId 科目id
     * @param score     成绩
     */
    @Insert("INSERT INTO rankings (user_id, subject_id, score) VALUES (#{userId}, #{subjectId}, #{score})")
    void insertRanking(@Param("userId") int userId, @Param("subjectId") int subjectId, @Param("score") int score);


    /**
     * 获取所有用户的做题数量排名，并添加分页支持
     *
     * @param offset 偏移量
     * @param limit  每页大小
     * @return 用户做题数量排名列表
     */
    @Select("SELECT " +
            "ar.user_id AS userId, " +
            "u.u_name AS userName, " +  // 修改为查询 u_name 字段
            "u.created_at AS createdAt, " +
            "COUNT(ar.question_id) AS totalAnswers, " +
            "SUM(ar.is_correct) AS correctAnswers, " +
            "ROUND((SUM(ar.is_correct) / COUNT(ar.question_id)) * 100, 2) AS correctRate " +  // 新增正确率字段，保留两位小数
            "FROM wechat_clone.user_answers ar " +  // 修正表名为 user_answers
            "JOIN wechat_clone.users u ON ar.user_id = u.id " +
            "GROUP BY ar.user_id " +
            "ORDER BY correctRate DESC " +  // 按照正确率排序，降序
            "LIMIT #{limit} OFFSET #{offset}")
    List<UserRankingDTO> getRankingsByQuestions(@Param("offset") int offset, @Param("limit") int limit);



}
